/**
 * Filename		:QLHCF234FuncInfo.java
 */
package quanlyhochieu.func234;
import utils.DateUtils;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;

import javax.faces.model.SelectItem;

import quanlyhochieu.QLHCF1234TableItemInfo;
import utils.UtilsOnScopes;
import dbutil.ConnectDB;
import exportExcel.ExportExcel;

/******************************************
 * Filename			: QLHCF234FuncInfo.java
 * Description		:
 * Created date		: Aug 16, 2008
 * Author			: Phonglt
 ******************************************/
public class QLHCF234FuncInfo {
	private String error;
	private String content1;
	private boolean content2;
	private String content3;
	private Date date1, date2;
	private int tongSo;
	private ArrayList<QLHCF1234TableItemInfo> items = new ArrayList<QLHCF1234TableItemInfo>();
	private ArrayList<HashMap<String, Object>> database = new ArrayList<HashMap<String,Object>>();
	private String linkDownload;
	private ArrayList<String> coquans = new ArrayList<String>();
	
	/****************************************************
	 * Method name		: getDate1 [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public Date getDate1() {
		return date1;
	}
	/****************************************************
	 * Method name		: setDate1 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setDate1(Date date1) {
		this.date1 = date1;
	}
	/****************************************************
	 * Method name		: getDate2 [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public Date getDate2() {
		return date2;
	}
	/****************************************************
	 * Method name		: setDate2 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setDate2(Date date2) {
		this.date2 = date2;
	}
	/****************************************************
	 * Method name		: getContent3 [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getContent3() {
		return content3;
	}
	/****************************************************
	 * Method name		: setContent3 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setContent3(String content3) {
		this.content3 = content3;
	}
	/****************************************************
	 * Method name		: getSize [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public int getSize() {
		return this.items.size();
	}
	/****************************************************
	 * Method name		: getError [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getError() {
		return error;
	}
	/****************************************************
	 * Method name		: setError [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setError(String error) {
		this.error = error;
	}
	/****************************************************
	 * Method name		: getContent1 [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getContent1() {
		return content1;
	}
	/****************************************************
	 * Method name		: setContent1 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setContent1(String content1) {
		this.content1 = content1;
	}
	/****************************************************
	 * Method name		: getContent2 [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public boolean getContent2() {
		return content2;
	}
	/****************************************************
	 * Method name		: setContent2 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setContent2(boolean content2) {
		this.content2 = content2;
	}
	/****************************************************
	 * Method name		: getItems [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public ArrayList<QLHCF1234TableItemInfo> getItems(String SQL) {
		this.items = new ArrayList<QLHCF1234TableItemInfo>();
		try {
			System.out.println(SQL);
			ArrayList<HashMap<String, Object>> arr = ConnectDB
					.getDataTypeList(SQL);
			if (arr.size() > 0) {
				ArrayList<QLHCF1234TableItemInfo> temp_items = new ArrayList<QLHCF1234TableItemInfo>(
						arr.size());
				for (int i = 1; i <= arr.size(); i++) {
					HashMap<String, Object> row = arr.get(i - 1);
					QLHCF1234TableItemInfo item = new QLHCF1234TableItemInfo(
							i,
							(String) row.get("sohc"),
							(String) row.get("loaihc"),
							(Date) row.get("ngaycap"),
							(Date) row.get("ngayhethan"),
							(String) row.get("noicap"),
							(String) row.get("cmnd"),
							(String) row.get("ten"),
							(String) row.get("namsinh"),
							((Boolean) row.get("gioitinh")) == true ? "Nam"
									: "Nữ",
							(String) row.get("coquan"),
							((Boolean) row.get("dangvien")) == true ? "Đảng viên"
									: "-", (String) row.get("chucvu"));
					temp_items.add(item);
				}
				this.setItems(temp_items);
				this.database = arr;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.setTongSo(items.size());
		return items;
	}

	/****************************************************
	 * Method name		: setItems [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setItems(ArrayList<QLHCF1234TableItemInfo> items) {
		this.items = items;
	}
	/****************************************************
	 * Method name		: getLinkDownload [GETTER]
	 * Return type		: QLHCF234FuncInfo
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getLinkDownload() {
		return linkDownload;
	}
	/****************************************************
	 * Method name		: setLinkDownload [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setLinkDownload(String linkDownload) {
		this.linkDownload = linkDownload;
	}
	/****************************************************
	 * Method name		: setDatabase [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 16, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public ArrayList<CoQuan> getCoQuans(){
		StringBuilder SQL = new StringBuilder();
		SQL.append(" SELECT count, A.sohc, A.loaihc, A.ngaycap, A.ngayhethan, A.noicap, B.* ");
		SQL.append(" FROM hochieu A, canhan B, (SELECT coquan, count(sohc) FROM hochieu I INNER JOIN canhan II ON I.cmnd = II.cmnd GROUP BY coquan) C ");
		SQL.append(" WHERE A.cmnd = B.cmnd AND B.coquan = C.coquan ");
		SQL.append(" ORDER BY coquan ");
		ArrayList<CoQuan> coquans = new ArrayList<CoQuan>();
		try {
			ArrayList<HashMap<String, Object>> arr = ConnectDB
					.getDataTypeList(SQL.toString());
			if (arr.size() > 0) {
				for (int i = 0; i < arr.size(); i++) {
					System.out.println("I ----------> " + i);
					HashMap<String, Object> row = arr.get(i);
					CoQuan cq = new CoQuan((String) row.get("coquan"));
					ArrayList<HoChieu> hochieus = new ArrayList<HoChieu>();
					long sohc = (Long)row.get("count");
					System.out.println(">>>>>----------------" + sohc) ;
					for (Long j = 0L; j < sohc; j++) {
						System.out.println("J1 ==> " + j);
						HoChieu hc = new HoChieu((String)row.get("sohc"), 
								(String)row.get("ten"), (String)row.get("coquan"), (String)row.get("loaihc"));
						System.out.println("I --> " + i);
						System.out.println(hc.getTen());
						System.out.println((String)row.get("coquan"));
						hochieus.add(hc);
						i++;
						row = arr.get(i);
						System.out.println("I2 0000000> " + i);
					}
					i--;
					System.out.println("<<<<-------");
					cq.setHochieus(hochieus);
					coquans.add(cq);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		
		return coquans;
	}
	
	/**************************************
	 * Method name		: getTableItemF03
	 * Return type		: ArrayList<QLHCF1234TableItemInfo>
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public ArrayList<QLHCF1234TableItemInfo> getTableItemF03(){
		StringBuilder SQL = new StringBuilder();
		SQL.append(" SELECT A.sohc, A.loaihc, A.ngaycap, A.ngayhethan, A.noicap, canhan.* ");
		SQL.append(" FROM hochieu A, canhan WHERE A.cmnd = canhan.cmnd ");
		SQL.append(" AND ngayhethan - 180 <= now()  ");
		SQL.append(" ORDER BY ngaycap ");
		return this.getItems(SQL.toString());
	}
	/**************************************
	 * Method name		: exportExel
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String exportExel3(){
		if(this.database != null && this.database.size() > 0){
			Hashtable<String, String> header = new Hashtable<String, String>();
			header.put("TONG",String.valueOf(this.getSize()));
		ExportExcel.exportJExcel_HaveGroup("quanlyhochieu_template",
				"hochieuhethan.xml", "exp_quanlyhochieu", this.database,
				UtilsOnScopes.getRequest(), header, null);
		this.setLinkDownload(ExportExcel.pathDownload);
		}else{
			this.setError("Không có dữ liệu được báo cáo.");
			return "qlhcfunc03";
		}
		return "download";
	}
	/**************************************
	 * Method name		: exportExel4
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String exportExel4(){
		if(this.database != null && this.database.size() > 0){
			Hashtable<String, String> header = new Hashtable<String, String>();
			header.put("TONG",String.valueOf(this.getSize()));
			header.put("THAOTAC", this.content3.equals("G") ? "GIAO" : "NHẬN");
			header.put("TU", this.formatDate(date1));
			header.put("DEN", this.formatDate(date2));
			ExportExcel.exportJExcel_HaveGroup("quanlyhochieu_template",
					"giaonhan_hc.xml", "exp_quanlyhochieu", this.database,
					UtilsOnScopes.getRequest(), header, null);
			this.setLinkDownload(ExportExcel.pathDownload);
		}else{
			this.setError("Không có dữ liệu được báo cáo.");
			return "qlhcfunc04";
		}
		return "download";
	}
	
	/**************************************
	 * Method name		: getTableItemF04
	 * Return type		: ArrayList<QLHCF1234TableItemInfo>
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public ArrayList<QLHCF1234TableItemInfo> getTableItemF04(){
		StringBuilder SQL = new StringBuilder();
		SQL.append(" SELECT A.sohc, A.loaihc, A.ngaycap, A.ngayhethan, A.noicap, canhan.* ");
		SQL.append(" FROM hochieu A, canhan WHERE A.cmnd = canhan.cmnd ");
		if (this.date1 != null && this.date2 != null && this.date1.compareTo(date2) <=0) {
			SQL.append(" AND sohc IN (SELECT sohc FROM nhatkytranhanhc ");
			SQL
					.append(" WHERE thaotac = '"+ this.content3 +"' AND (ngaythaotac BETWEEN '" + DateUtils.utilDateToSqlDate(this.date1) + "' AND '" + DateUtils.utilDateToSqlDate(this.date2) + "')) ");
		} 
		SQL.append(" ORDER BY ngaycap ");
		
		return this.getItems(SQL.toString());
	}
	 
	/**************************************
	 * Method name		: searchFunc04
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String searchFunc04(){
		System.out.println(date1);
		System.out.println(date2);
		System.out.println(content3);
		return "qlhcfunc04";
	}
	
	@SuppressWarnings("deprecation")
	private String formatDate(Date date) {
		return  (date.getYear()+ 1900) + "-" +  (date.getMonth() + 1) + "-" + date.getDate();
	}
	
	/**************************************
	 * Method name		: comeback3
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String comeback3(){
		return "qlhcfunc03";
	}
	/**************************************
	 * Method name		: comeback4
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String comeback4(){
		return "qlhcfunc04";
	}
	/**************************************
	 * Method name		: comeback2
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String comeback2(){
		return "qlhcfunc02";
	}
	
	public SelectItem[] getCoquanItemSelect(){
		this.coquans = new ArrayList<String>();
		String SQL = "SELECT DISTINCT coquan FROM canhan ORDER BY coquan";
		ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
		ArrayList<String> coquans = new ArrayList<String>();
		if (arr.size() > 0) {
			SelectItem[] items = new SelectItem[arr.size()+1];
			items[0] = new SelectItem(0,"---------Chọn--------");
			coquans.add("-------Chọn--------");
			for (int i = 1; i < items.length; i++) {
				HashMap<String, Object> row = arr.get(i-1);
				items[i] = new SelectItem(i, (String)row.get("coquan"));
				coquans.add(((String)row.get("coquan")).trim());
			}
			this.coquans = coquans;
			return items;
		} else {
			return new SelectItem[] { new SelectItem("-1",
					"<Không có cơ quan nào>") };
		}
	}
	
	/**************************************
	 * Method name		: getTableItemF02
	 * Return type		: ArrayList<QLHCF1234TableItemInfo>
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public ArrayList<QLHCF1234TableItemInfo> getTableItemF02(){
		System.out.println("combo:" +this.content1);
		System.out.println("check:" +this.content2);
		StringBuilder SQL = new StringBuilder();
		SQL.append("SELECT HC.sohc, HC.loaihc, HC.ngaycap, HC.ngayhethan, HC.noicap, CN.* ") ;
		//SQL.append(" SELECT A.sohc, A.loaihc, A.ngaycap, A.ngayhethan, A.noicap, canhan.* ");
		//SQL.append(" FROM hochieu A, canhan WHERE A.cmnd = canhan.cmnd ");
		if (this.content1 != null) {
			if (this.content2){
			SQL.append(" FROM hochieu as HC,canhan as CN, vitriluutru as VT WHERE HC.cmnd = CN.cmnd and VT.sohc = HC.sohc and VT.trangthainhan = 'G'");
			SQL.append(" AND CN.coquan = '" + this.coquans.get(Integer.valueOf(this.content1)) + "' ");
			//SQL.append(" AND (SELECT thaotac FROM nhatkytranhanhc I WHERE I.sohc = A.sohc ORDER BY I.ngaythaotac DESC LIMIT 1) LIKE 'G' ");
			}
			else {
				SQL.append(" FROM hochieu as HC, canhan as CN WHERE HC.cmnd = CN.cmnd ");
				SQL.append(" AND CN.coquan = '" + this.coquans.get(Integer.valueOf(this.content1)) + "' ");
			}
				
		}
		return this.getItems(SQL.toString());
	}

	
	public int getTongSo() {
		return tongSo;
	}
	public void setTongSo(int tongSo) {
		this.tongSo = tongSo;
	}
	/**************************************
	 * Method name		: searchFunc02
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String searchFunc02(){
		return "qlhcfunc02";
	}
	
	/**************************************
	 * Method name		: exportExel2
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 17, 2008
	 * Author			: Phonglt
	 **************************************/
	public String exportExel2(){
		if(this.database != null && this.database.size() > 0){
			Hashtable<String, String> header = new Hashtable<String, String>();
			header.put("TONG",String.valueOf(this.getSize()));
			header.put("COQUAN", (String)this.database.get(0).get("coquan"));
			header.put("LOAI", this.content2? "CHƯA NỘP VỀ" : "HIỆN CÓ");
		ExportExcel.exportJExcel_HaveGroup("quanlyhochieu_template",
				"hc_trongcoquan.xml", "exp_quanlyhochieu", this.database,
				UtilsOnScopes.getRequest(), header, null);
		this.setLinkDownload(ExportExcel.pathDownload);
		}else{
			this.setError("Không có dữ liệu được báo cáo.");
			return "qlhcfunc02";
		}
		return "download";
	}
	public String Reset(){
		this.date1 = null;
		this.date2 = null;
		this.setItems(new ArrayList<QLHCF1234TableItemInfo>());
		return "qlhcfunc04";
	}
}
